using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Reflection;

namespace PGMapper
{
    /// <summary>
    /// Allows manipulating a table in the database as if it were a collection of objects
    /// of type T with properties corresponding to the database columns (ActiveRecord pattern).
    /// Implements CRUD.
    /// </summary>
    public abstract class Mapped<T>
        where T:Mapped<T>
    {
        static MappedType<T> mapping;

        static Mapped()
        {
            mapping = new MappedType<T>();
        }

        protected static PostgresDatabase DB { get { return PostgresDatabase.SharedDatabase; } }

        protected Mapped() { }

        #region Create

        /// <summary>
        /// Create a new tracked object of type T. It will not exist in the database until
        /// you save it.
        /// </summary>
        public static T Create()
        {
            return mapping.CreateInstance(DB);
        }

        #endregion

        #region Find

        /// <summary>
        /// Executes a sql statement expected to return zero or one rows, and convert
        /// that row into an instance of type T.
        /// </summary>
        public static T Find(string sql, params object[] positionalParameters)
        {
            PostgresRow row = DB.ExecuteRow(sql, positionalParameters);
            return row != null ? mapping.CreateInstance(DB, row) : default(T);
        }

        /// <summary>
        /// Shortcut for the most common case of finding an object using a column named "id".
        /// </summary>
        public static T Find(int id)
        {
            return FindWhere(mapping.SerialColumn.Column.Name + " = ?", id);
        }

        /// <summary>
        /// Executes a "select * from [table] [where]" sql statement expected to return 
        /// zero or one rows, and convert that row into an instance of type T.
        /// </summary>
        public static T FindWhere(string where, params object[] positionalParameters)
        {
            return Find("select * from " + mapping.Table.Name + " where " + where, 
                positionalParameters);
        }

        /// <summary>
        /// Shortcut for the most common case of finding an object using a column named "id".
        /// Expects the object to exist and to be unique.
        /// </summary>
        public static T FindOne(int id)
        {
            return FindOneWhere(mapping.SerialColumn.Column.Name + " = ?", id);
        }

        /// <summary>
        /// Executes a sql statement expected to return exactly one row, and convert
        /// that row into an instance of type T.
        /// </summary>
        public static T FindOne(string sql, params object[] positionalParameters)
        {
            return mapping.CreateInstance(DB, DB.ExecuteOneRow(sql, positionalParameters));
        }

        /// <summary>
        /// Executes a "select * from [table] [where]" sql statement expected to return 
        /// exactly one row, and convert that row into an instance of type T.
        /// </summary>
        public static T FindOneWhere(string where, params object[] positionalParameters)
        {
            return FindOne("select * from " + mapping.Table.Name + " where " + where,
                positionalParameters);
        }

        /// <summary>
        /// Executes a sql statement returning zero or more rows and converts all rows
        /// into instances of type T.
        /// </summary>
        public static List<T> FindAll(string sql, params object[] positionalParameters)
        {
            var list = new List<T>();

            using (IDataReader reader = DB.ExecuteReader(sql, positionalParameters))
                while (reader.Read())
                {
                    var row = new PostgresRow(reader);
                    list.Add(mapping.CreateInstance(DB, row));
                }

            return list;
        }

        /// <summary>
        /// Executes a "SELECT * FROM [table] [where]" sql statement expected to return 
        /// any number of rows, and converts the results into instances of type T.
        /// </summary>
        public static List<T> FindAllWhere(string where, params object[] positionalParameters)
        {
            return FindAll("select * from " + mapping.Table.Name + " where " + where,
                positionalParameters);
        }

        #endregion

        #region Save

        /// <summary>
        /// Saves this object into the database; it will be either inserted or
        /// updated depending on how it was constructed. Only properties that have changed
        /// will be updated.
        /// </summary>
        public void Save()
        {
            ITrackChanges objWithChanges = this as ITrackChanges;

            if (objWithChanges == null)
                throw new Exception("Cannot save an object that was not created by this class (must implement ITrackChanges or let us do it for you).");

            if (objWithChanges.Tracker.ExistsInDatabase)
                Update(objWithChanges.Tracker.ChangedProperties);
            else
                Insert(objWithChanges.Tracker.ChangedProperties);

            // it definitely exists in the database now so don't try to insert it again when you Save()
            objWithChanges.Tracker.ExistsInDatabase = true;
            objWithChanges.Tracker.ClearChanges(); // keep track of changes from now on
        }

        /// <summary>
        /// Insert a row into the database based on the values of the given object.
        /// </summary>
        private void Insert(IEnumerable<string> changedProperties)
        {
            // if we have a serial column and you haven't manually set it yourself, auto-assign
            // the next value in the associated sequence.
            if (mapping.HasSerialColumn && !changedProperties.Contains(mapping.SerialColumn.Property.Name))
                AssignNextSequence();

            var columns = new StringBuilder();
            var questions = new StringBuilder();
            var comma = "";

            object[] values = mapping.GetColumnValues((T)this, changedProperties);
            int valueIndex = 0;

            // build sql statement
            foreach (string propertyName in changedProperties)
            {
                columns.Append(comma);
                columns.AppendFormat(mapping.GetColumnName(propertyName));
                questions.Append(comma);
                // careful about null values - if we pass ? with a null value, Npgsql
                // can't figure out the type of the parameter
                questions.Append(values[valueIndex] != null ? "?" : "null");
                comma = ", ";
                valueIndex++;
            }

            string sql = "insert into " + mapping.Table.Name
                + "(" + columns + ")" + " values (" + questions + ")";

            // create the row
            DB.Execute(sql, values.Where(o => o != null).ToArray());
        }

        /// <summary>
        /// Updates all properties of the given object in the database based on the mapped
        /// table's SERIAL value.
        /// </summary>
        private void Update(IEnumerable<string> changedProperties)
        {
            if (!mapping.HasSerialColumn)
                throw new Exception("Don't yet know how to update an object without a serial column.");

            var setSql = new StringBuilder();
            var comma = "";

            object[] values = mapping.GetColumnValues((T)this, changedProperties);
            int valueIndex = 0;
            if (values.Length == 0) return; // no properties changed!

            foreach (string propertyName in changedProperties)
            {
                setSql.Append(comma);
                setSql.AppendFormat(mapping.GetColumnName(propertyName));
                // careful about null values - if we pass ? with a null value, Npgsql
                // can't figure out the type of the parameter
                setSql.Append(values[valueIndex++] != null ? " = ?" : " = null");
                comma = ", ";
            }

            int id = (int)mapping.SerialColumn.Get(this);

            Update(id, setSql.ToString(), values.Where(o => o != null).ToArray());
        }

        /// <summary>
        /// Set the serial property (assumed to exist) on the given object of type T based
        /// on the related sequence.
        /// </summary>
        private void AssignNextSequence()
        {
            string sequence = mapping.GetSerialSequenceName();

            int id = DB.ExecuteSingle<int>("select nextval('" + sequence + "')");

            // set the given object's serial property
            mapping.SerialColumn.Set(this, id);
        }

        #endregion

        #region Update

        /// <summary>
        /// Shortcut for the most common case of updating an object using a SERIAL column named "id".
        /// </summary>
        public static void Update(int id, string setSql, params object[] positionalParameters)
        {
            UpdateAll(setSql + " where " + mapping.SerialColumn.Column.Name + " = " + id, positionalParameters);
        }

        /// <summary>
        /// Executes an "UPDATE [table] SET [setAndWhereSql]" on the table mapped to type T.
        /// </summary>
        public static void UpdateAll(string setAndWhereSql, params object[] positionalParameters)
        {
            DB.Execute("update " + mapping.Table.Name + " set " + setAndWhereSql, positionalParameters);
        }

        #endregion

        #region Delete

        /// <summary>
        /// Delete a row in the database based on the mapped SERIAL column in the given object.
        /// </summary>
        public void Delete()
        {
            if (mapping.HasSerialColumn)
                DeleteAllWhere(mapping.SerialColumn.Column.Name + " = ?", mapping.SerialColumn.Get(this));
            else
                throw new Exception("Don't yet know how to delete a row in a table without a SERIAL column.");
        }

        /// <summary>
        /// Shortcut for the most common case of deleting an object using a SERIAL column named "id".
        /// </summary>
        public static void Delete(int id)
        {
            DeleteAllWhere(mapping.SerialColumn.Column.Name + " = ?", id);
        }

        /// <summary>
        /// Are you crazy?!?
        /// </summary>
        public static void DeleteAll()
        {
            DB.Execute("delete from " + mapping.Table.Name);
        }

        /// <summary>
        /// Delete all rows in this table matching the given condition.
        /// </summary>
        public static void DeleteAllWhere(string where, params object[] positionalParameters)
        {
            DB.Execute("delete from " + mapping.Table.Name + " where " + where,
                positionalParameters);
        }

        #endregion
    }
}
